﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using uMES.LeanManufacturing.DBUtility;
using uMES.LeanManufacturing.ParameterDTO;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESMfgSetUpBusiness
    {
        #region 更新生产准备信息
        /// <summary>
        /// 更新生产准备信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public Boolean UpdateMfgSetUpInfo(string strID, Dictionary<string, string> para)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("UPDATE mfgsetupinfo SET");
            strSQL.AppendLine(string.Format("dispatchinfoid = '{0}',", para["DispatchInfoID"]));
            strSQL.AppendLine(string.Format("documents = {0},", para["Documents"]));
            strSQL.AppendLine(string.Format("tools = {0},", para["Tools"]));
            strSQL.AppendLine(string.Format("cuttingtools = {0},", para["CuttingTools"]));
            strSQL.AppendLine(string.Format("ncprogram = {0},", para["NCProgram"]));
            strSQL.AppendLine(string.Format("resources = {0},", para["Resources"]));
            strSQL.AppendLine(string.Format("material = {0},", para["Material"]));
            strSQL.AppendLine(string.Format("status = {0},", para["Status"]));
            strSQL.AppendLine(string.Format("notes = '{0}',", para["Notes"]));
            strSQL.AppendLine(string.Format("submitemployeeid = '{0}',", para["SubmitEmployeeID"]));
            strSQL.AppendLine(string.Format("submitdate = TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS')", para["SubmitDate"]));
            strSQL.AppendLine(string.Format("WHERE ID = '{0}'", strID));

            OracleHelper.ExecuteSql(strSQL.ToString());

            return true;
        }
        #endregion

        #region 添加生产准备信息
        /// <summary>
        /// 添加生产准备信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public Boolean AddMfgSetUpInfo(Dictionary<string, string> para)
        {
            string strID = Guid.NewGuid().ToString();
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("INSERT INTO mfgsetupinfo(Id,dispatchinfoid,documents,tools,cuttingtools,ncprogram,resources,material,");
            strSQL.AppendLine("     status,notes,submitemployeeid,submitdate)");
            strSQL.AppendLine("VALUES(");
            strSQL.AppendLine(string.Format("'{0}',", strID));
            strSQL.AppendLine(string.Format("'{0}',", para["DispatchInfoID"]));
            strSQL.AppendLine(string.Format("{0},", para["Documents"]));
            strSQL.AppendLine(string.Format("{0},", para["Tools"]));
            strSQL.AppendLine(string.Format("{0},", para["CuttingTools"]));
            strSQL.AppendLine(string.Format("{0},", para["NCProgram"]));
            strSQL.AppendLine(string.Format("{0},", para["Resources"]));
            strSQL.AppendLine(string.Format("{0},", para["Material"]));
            strSQL.AppendLine(string.Format("{0},", para["Status"]));
            strSQL.AppendLine(string.Format("'{0}',", para["Notes"]));
            strSQL.AppendLine(string.Format("'{0}',", para["SubmitEmployeeID"]));
            strSQL.AppendLine(string.Format("TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS')", para["SubmitDate"]));
            strSQL.AppendLine(")");

            OracleHelper.ExecuteSql(strSQL.ToString());

            return true;
        }
        #endregion

        #region 获取派工任务
        #region 分页查询
        public uMESPagingDataDTO GetSourceData(Dictionary<string, string> para, int intPageIndex, int intPageSize)
        {
            string strSQL = GetSQL_D(para);

            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strSQL, intPageIndex, intPageSize);
            return retR;
        }
        #endregion

        #region 不分页查询，用于导出
        public DataTable GetAllDataForOutExcel(Dictionary<string, string> para)
        {
            string strSQL = GetSQL_D(para);

            DataTable DT = OracleHelper.GetDataTable(strSQL);
            return DT;
        }
        #endregion

        #region 组合查询语句
        protected string GetSQL_D(Dictionary<string, string> para)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT mo.processno,mo.oprno,c.containername,pb.productname,p.description,");
            strQuery.AppendLine("   di.qty,sb.specname,t.teamname,di.plannedcompletiondate,");
            strQuery.AppendLine("   di.id,di.teamid,c.qty AS contaiernqty,c.containerid,di.workflowid,di.specid,di.parentid,");
            strQuery.AppendLine("   c.plannedstartdate AS containerstartdate,c.plannedcompletiondate AS containercompletiondate,");
            strQuery.AppendLine("   r.resourcename,di.resourceid,di1.qty AS parentqty,c.productid,msi.id AS setupid,");
            strQuery.AppendLine("   msi.documents,msi.tools,msi.cuttingtools,msi.ncprogram,msi.resources,msi.material");
            strQuery.AppendLine("FROM dispatchinfo di");
            strQuery.AppendLine("LEFT JOIN container c ON c.containerid = di.containerid");
            strQuery.AppendLine("LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid");
            strQuery.AppendLine("LEFT JOIN product p ON p.productid = c.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = di.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("LEFT JOIN team t ON t.teamid = di.teamid");
            strQuery.AppendLine("LEFT JOIN resourcedef r ON r.resourceid = di.resourceid");
            strQuery.AppendLine("LEFT JOIN dispatchinfo di1 ON di1.id = di.parentid");
            strQuery.AppendLine("LEFT JOIN mfgsetupinfo msi ON msi.dispatchinfoid = di.id");
            strQuery.AppendLine("WHERE c.status = 1");
            strQuery.AppendLine("AND c.parentcontainerid IS NULL");
            strQuery.AppendLine("AND (msi.status = 0 OR msi.status IS NULL)");

            if (para.Keys.Contains("DispatchType"))
            {
                if (!string.IsNullOrEmpty(para["DispatchType"]))
                {
                    strQuery.AppendLine(string.Format("AND di.dispatchtype = {0}", para["DispatchType"]));
                    //strQuery.AppendLine(string.Format("AND di.dispatchtotype = {0}", para["DispatchType"]));
                }
            }

            if (para.Keys.Contains("FactoryID"))
            {
                if (!string.IsNullOrEmpty(para["FactoryID"]))
                {
                    //strQuery.AppendLine(string.Format("AND c.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("TeamID"))
            {
                if (!string.IsNullOrEmpty(para["TeamID"]))
                {
                    strQuery.AppendLine(string.Format("AND di.teamid = '{0}'", para["TeamID"]));
                }
            }
            if (para.Keys.Contains("Status"))
            {
                if (!string.IsNullOrEmpty(para["Status"]))
                {
                    strQuery.AppendLine(string.Format("AND di.status = {0}", para["Status"]));
                }
            }

            if (para.Keys.Contains("ScanContainerName")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty(para["ScanContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND c.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty(para["ProcessNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(mo.processno) LIKE '%{0}%'", para["ProcessNo"].ToLower()));
                }
            }
            if (para.Keys.Contains("ContainerName")) //批次号
            {
                if (!string.IsNullOrEmpty(para["ContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(c.containername) LIKE '%{0}%'", para["ContainerName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty(para["ProductName"]))
                {
                    strQuery.AppendLine(string.Format("AND (LOWER(pb.productname) LIKE '%{0}%' OR LOWER(p.description) LIKE '%{0}%')", para["ProductName"].ToLower()));
                }
            }
            if (para.Keys.Contains("SpecName")) //工序
            {
                if (!string.IsNullOrEmpty(para["SpecName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(sb.specname) LIKE '%{0}%'", para["SpecName"].ToLower()));
                }
            }
            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty(para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND di.plannedcompletiondate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty(para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND di.plannedcompletiondate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }

            strQuery.AppendLine("ORDER BY di.dispatchdate DESC");

            return strQuery.ToString();
        }
        #endregion
        #endregion
    }
}
